﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Reflection;
using System.Windows.Forms;

namespace NDK.Utils
{
    public class SQLiteUtils
    {
        private static log4net.ILog m_Logger = log4net.LogManager.GetLogger("NDK.Utils.SQLiteUtils");
        //static string connString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
        public static string _connectionString = "Data Source=" + Environment.CurrentDirectory + "\\Config\\test1.db;password=123456";
        public static SQLiteConnection _connection = new SQLiteConnection(_connectionString);
        public static bool db_read_lock = false;
        public static bool db_write_lock = false;
        #region 获取数据库连接
        /// <summary>
        /// 获取数据库连接
        /// </summary>
        /// <param name="dbName">数据库名称</param>
        public static SQLiteConnection GetConnection(string dbName)
        {
            SQLiteConnection conn = null;
            string strSQLiteDB = Environment.CurrentDirectory+"\\Config\\";
            try
            {
                string dbPath = "Data Source=" + strSQLiteDB + "\\" + dbName + ".db";
                conn = new SQLiteConnection(dbPath);
                conn.SetPassword("123456");
                conn.Open();
                return conn;
            }
            catch (Exception ex)
            {
                //CloseDB(conn);
                throw new Exception("数据库打开异常！" + ex.StackTrace);
            }
        }

        /// <summary>
        /// 获取数据库连接
        /// </summary>
        /// <param name="dbName">数据库名称</param>
        //public static SQLiteConnection GetConnection()
        //{
        //    SQLiteConnection conn = null;
        //    try
        //    {
        //        string dbPath = connString;
        //        conn = new SQLiteConnection(dbPath);
        //        conn.Open();
        //        return conn;
        //    }
        //    catch (Exception ex)
        //    {
        //        CloseDB(conn);
        //        throw new UserException("数据库打开异常！" + ex.StackTrace);
        //    }
        //}
        #endregion

        #region 关闭数据库连接
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <param name="conn">数据库连接</param>
        public static void CloseDB(SQLiteConnection conn)
        {
            if (conn.State == ConnectionState.Connecting)
            {
                try
                {
                    conn.Close();
                    SQLiteConnection.ClearAllPools();
                }
                catch (System.Exception ex)
                {
                    throw new Exception("数据库关闭异常！" + ex.StackTrace);
                }
            }

        }
        #endregion

        #region 删除选中DataGridView列的数据
        /// <summary>
        /// 删除选中DataGridView列的数据
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="dgvSelectedRowList"></param>
        public static void DeleteToInputSettingTable(SQLiteConnection conn, List<string> dgvSelectedRowList)
        {
            string sql = "DELETE FROM InputSetting WHERE " + "Input名称 = " + "'" + dgvSelectedRowList[1] + "'";
            SQLiteCommand cmdInsert = new SQLiteCommand(sql, conn);
            try
            {
                cmdInsert.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                //CloseDB(conn);
                throw new Exception("删除InputSetting表中数据时发生异常！SQL：" + sql + "Exception Message：" + ex.Message);
            }
        }
        #endregion

        #region DataGridView绑定DataSource
        /// <summary>
        /// DataGridView绑定DataSource
        /// </summary>
        /// <param name="dataGridView">指定DataGridView</param>
        /// <param name="dataAdapter"></param>
        /// <param name="dbName">数据库名称</param>
        /// <param name="tableName">表名</param>
        public static DataTable DataSourceBindToDataGridView(DataGridView dataGridView,
            out SQLiteDataAdapter dataAdapter,
            string dbName,
            string tableName,
            string condition = "")
        {

            SQLiteConnection conn = GetConnection(dbName);
            SQLiteCommand selectCommand = null;

            SQLiteConnectionStringBuilder sb = new SQLiteConnectionStringBuilder();

            selectCommand = conn.CreateCommand();
            selectCommand.CommandText = "SELECT * FROM " + tableName;

            if (condition != "" && condition != null)
            {
                selectCommand.CommandText = "SELECT * FROM " + tableName + " where " + condition;
            }

            dataAdapter = new SQLiteDataAdapter();
            dataAdapter.SelectCommand = selectCommand;
            DataTable dt = new DataTable();
            try
            {
                dataAdapter.Fill(dt);
                dataGridView.DataSource = dt.DefaultView;

                SQLiteCommandBuilder builder = new SQLiteCommandBuilder(dataAdapter);
                dataAdapter.UpdateCommand = builder.GetUpdateCommand();
                dataAdapter.InsertCommand = builder.GetInsertCommand();
                dataAdapter.DeleteCommand = builder.GetDeleteCommand();

                return dt;
            }
            catch (Exception e)
            {
                m_Logger.Error("数据源绑定Datagridview失败！SQL：" + selectCommand.CommandText + "Exception Message：" + e.Message);
                return null;
            }
        }
        #endregion

        #region DataGridView绑定DataSource
        /// <summary>
        /// DataGridView绑定DataSource
        /// </summary>
        /// <param name="dataGridView">指定DataGridView</param>
        /// <param name="dataAdapter"></param>
        /// <param name="dbName">数据库名称</param>
        /// <param name="tableName">表名</param>
        /// <param name="strWhere">筛选条件</param>
        /// <param name="fields">字段</param>
        public static DataTable DataSourceBindToDataGridView(DataGridView dataGridView,
            out SQLiteDataAdapter dataAdapter,
            string dbName,
            string tableName,
            string[] fields,
            string[] strWhere)
        {
            SQLiteConnection conn = GetConnection(dbName);
            SQLiteCommand selectCommand = null;

            SQLiteConnectionStringBuilder sb = new SQLiteConnectionStringBuilder();

            selectCommand = conn.CreateCommand();
            selectCommand.CommandText = "SELECT * FROM " + tableName + " WHERE 1=1";
            for (int i = 0; i < fields.Length; i++)
            {
                selectCommand.CommandText += " and " + fields[i] + " = '" + strWhere[i] + "'";
            }
            dataAdapter = new SQLiteDataAdapter();
            dataAdapter.SelectCommand = selectCommand;
            DataTable dt = new DataTable();
            try
            {
                dataAdapter.Fill(dt);
                dataGridView.DataSource = dt;

                SQLiteCommandBuilder builder = new SQLiteCommandBuilder(dataAdapter);
                dataAdapter.UpdateCommand = builder.GetUpdateCommand();
                dataAdapter.InsertCommand = builder.GetInsertCommand();
                dataAdapter.DeleteCommand = builder.GetDeleteCommand();

                return dt;
            }
            catch (Exception e)
            {
                m_Logger.Error("数据源绑定Datagridview失败！SQL：" + selectCommand.CommandText + "Exception Message：" + e.Message);
                return null;
            }
        }

        #endregion

        public static DataTable GetSPC(string dbName, string tableName, string condition = "")
        {
            SQLiteConnection conn = GetConnection(dbName);
            SQLiteCommand selectCommand = null;

            SQLiteConnectionStringBuilder sb = new SQLiteConnectionStringBuilder();

            selectCommand = conn.CreateCommand();
            selectCommand.CommandText = "SELECT  SizeName , NormValue , UpperDeviation , LowerDeviation FROM " + tableName + " where turnPan = 0" + " and project = 'Book'";

            try
            {
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(selectCommand);
                DataTable dt = new DataTable();
                adapter.Fill(dt);

                return dt;
            }
            catch (Exception e)
            {
                m_Logger.Error("数据源绑定Datagridview失败！SQL：" + selectCommand.CommandText + "Exception Message：" + e.Message);
                return null;
            }
        }

        public static void DataSourceBindToDataGridView_Update(string dbName, string tableName, string SizeName, string NormValue,
                                string UpperDeviation, string LowerDeviation, string IsShow, string IsJudging, string turnPanNum)
        {
            SQLiteConnection conn = GetConnection(dbName);
            SQLiteCommand selectCommand = null;

            SQLiteConnectionStringBuilder sb = new SQLiteConnectionStringBuilder();

            selectCommand = conn.CreateCommand();
            try
            {
                selectCommand.CommandText = "Update " + tableName + " Set " + "NormValue = " + NormValue + " , " + "UpperDeviation = " + UpperDeviation + " , " + "LowerDeviation = " + LowerDeviation + " , " + "IsShow = " + "'" + IsShow + "'" + " , " + "IsJudging = " + "'" + IsJudging + "'" + " WHERE  SizeName = " + "'" + SizeName + "'" + " And turnPan = " + "'" + turnPanNum + "'";
                selectCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                m_Logger.Error("数据源绑定Datagridview失败！SQL：" + selectCommand.CommandText + "Exception Message：" + e.Message);
            }
        }

        #region 公用方法

        public static int GetMaxID(string FieldName, string TableName)
        {
            string strsql = "select max(" + FieldName + ")+1 from " + TableName;
            object obj = GetSingle(strsql);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }
        public static bool Exists(string strSql)
        {
            object obj = GetSingle(strSql);
            int cmdresult;
            if ((Equals(obj, null)) || (Equals(obj, DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        public static bool Exists(string strSql, params SQLiteParameter[] cmdParms)
        {
            object obj = GetSingle(strSql, cmdParms);
            int cmdresult;
            if ((Equals(obj, null)) || (Equals(obj, DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        public static bool ClearTable(string tableName)
        {
            string sql = "delete from " + tableName;
            try
            {
                ExecuteSql(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }

        #endregion

        #region  执行简单SQL语句

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString)
        {
            //using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(SQLString, _connection))
                {
                    try
                    {
                        if (_connection.State != ConnectionState.Open)
                            _connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (System.Data.SQLite.SQLiteException E)
                    {
                        _connection.Close();
                        throw new Exception(E.Message);
                    }
                }
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>		
        public static void ExecuteSqlTran(ArrayList SQLStringList)
        {
            using (SQLiteConnection conn = new SQLiteConnection(_connectionString))
            {
                conn.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = conn;
                SQLiteTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (System.Data.SQLite.SQLiteException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }
        /// <summary>
        /// 执行带一个存储过程参数的的SQL语句。
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString, string content)
        {
            using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
            {
                SQLiteCommand cmd = new SQLiteCommand(SQLString, connection);
                SQLiteParameter myParameter = new SQLiteParameter("@content", DbType.String);
                myParameter.Value = content;
                cmd.Parameters.Add(myParameter);
                try
                {
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (System.Data.SQLite.SQLiteException E)
                {
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }
        /// <summary>
        /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
        {
            using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
            {
                SQLiteCommand cmd = new SQLiteCommand(strSQL, connection);
                SQLiteParameter myParameter = new SQLiteParameter("@fs", DbType.Binary);
                myParameter.Value = fs;
                cmd.Parameters.Add(myParameter);
                try
                {
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (System.Data.SQLite.SQLiteException E)
                {
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString)
        {
            using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Equals(obj, null)) || (Equals(obj, DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SQLite.SQLiteException e)
                    {
                        connection.Close();
                        throw new Exception(e.Message);
                    }
                }
            }
        }
        /// <summary>
        /// 执行查询语句，返回SQLiteDataReader
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SQLiteDataReader</returns>
        public static SQLiteDataReader ExecuteReader(string strSQL)
        {
            SQLiteConnection connection = new SQLiteConnection(_connectionString);
            SQLiteCommand cmd = new SQLiteCommand(strSQL, connection);
            try
            {
                connection.Open();
                SQLiteDataReader myReader = cmd.ExecuteReader();
                return myReader;
            }
            catch (System.Data.SQLite.SQLiteException e)
            {
                throw new Exception(e.Message);
            }

        }
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString)
        {
            //connection =
            //connectionString = "Data Source=" + Environment.CurrentDirectory + "\\test1.db;password=123456";
            //using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    if (_connection.State != ConnectionState.Open)
                        _connection.Open();
                    //WaitForRead();
                    SQLiteDataAdapter command = new SQLiteDataAdapter(SQLString, _connection);
                    command.Fill(ds, "ds");
                    //ReleaseReadLock();
                }
                catch (System.Data.SQLite.SQLiteException ex)
                {
                    //ReleaseReadLock();
                    throw new Exception(ex.StackTrace);
                }
                return ds;
            }
        }


        #endregion

        #region 执行带参数的SQL语句

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString, params SQLiteParameter[] cmdParms)
        {
            //using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, _connection, null, SQLString, cmdParms);
                        //WaitForWrite();
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        //ReleaseWriteLock();
                        return rows;
                    }
                    catch (System.Data.SQLite.SQLiteException E)
                    {
                        //ReleaseWriteLock();
                        throw new Exception(E.Message);
                    }
                }
            }
        }


        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SQLiteParameter[]）</param>
        public static void ExecuteSqlTran(Hashtable SQLStringList)
        {
            //using (SQLiteConnection conn = new SQLiteConnection(_connectionString))
            //{
            //    conn.Open();
            using (SQLiteTransaction trans = _connection.BeginTransaction())
            {
                SQLiteCommand cmd = new SQLiteCommand();
                try
                {
                    //循环
                    foreach (DictionaryEntry myDE in SQLStringList)
                    {

                        ExecuteSql(myDE.Key.ToString().Split('_')[0], (SQLiteParameter[])myDE.Value);

                    }
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw ex;
                }

            }
        }


        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString, params SQLiteParameter[] cmdParms)
        {
            using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        if ((Equals(obj, null)) || (Equals(obj, DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SQLite.SQLiteException e)
                    {
                        throw new Exception(e.Message);
                    }
                }
            }
        }

        /// <summary>
        /// 执行查询语句，返回SQLiteDataReader
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SQLiteDataReader</returns>
        public static SQLiteDataReader ExecuteReader(string SQLString, params SQLiteParameter[] cmdParms)
        {
            SQLiteConnection connection = new SQLiteConnection(_connectionString);
            SQLiteCommand cmd = new SQLiteCommand();
            try
            {
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                SQLiteDataReader myReader = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                return myReader;
            }
            catch (System.Data.SQLite.SQLiteException e)
            {
                throw new Exception(e.Message);
            }

        }

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString, params SQLiteParameter[] cmdParms)
        {
            //connectionString = "Data Source=" + Environment.CurrentDirectory + "\\test1.db;password=123456";
            //using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
            {
                //WaitForRead();
                SQLiteCommand cmd = new SQLiteCommand();
                PrepareCommand(cmd, _connection, null, SQLString, cmdParms);
                using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                        //ReleaseReadLock();
                    }
                    catch (System.Data.SQLite.SQLiteException ex)
                    {
                        //ReleaseReadLock();
                        throw new Exception(ex.StackTrace);
                    }
                    return ds;
                }
            }
        }


        private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, string cmdText, SQLiteParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {
                foreach (SQLiteParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

        #endregion

        #region COA
        public static List<T> GetList<T>(string cmdText)
        {
            DataTable dt = ExcuteCmd_DataTable(cmdText);
            List<T> list = new List<T>();
            try
            {
                Type type = typeof(T);
                foreach (DataRow itemRow in dt.Rows)
                {
                    T listitem = (T)Activator.CreateInstance(type);
                    Type t = listitem.GetType();
                    object val;
                    foreach (DataColumn itemCol in dt.Columns)
                    {
                        val = itemRow[itemCol];
                        val = val == null ? "" : val.ToString().Trim();
                        val = val == DBNull.Value ? "" : val.ToString().Trim();
                        PropertyInfo info = t.GetProperty(itemCol.ColumnName);
                        if (info == null || info.PropertyType == null)
                        {
                            continue;
                        }
                        string _t = info.PropertyType.FullName;
                        if (_t == typeof(double).ToString())
                        {
                            if (string.IsNullOrWhiteSpace(val.ToString())) val = 0;
                            info.SetValue(listitem, Convert.ChangeType(val, typeof(double)), null);
                        }
                        else if (_t == typeof(int).ToString())
                        {
                            if (string.IsNullOrWhiteSpace(val.ToString())) val = 0;
                            info.SetValue(listitem, Convert.ChangeType(val, typeof(int)), null);
                        }
                        else if (_t == typeof(float).ToString())
                        {
                            if (string.IsNullOrWhiteSpace(val.ToString())) val = 0;
                            info.SetValue(listitem, Convert.ChangeType(val, typeof(float)), null);
                        }
                        else if (_t == typeof(DateTime).ToString())
                        {
                            info.SetValue(listitem, Convert.ChangeType(val, typeof(DateTime)), null);
                        }
                        else if (_t == typeof(bool).ToString())
                        {
                            if (string.IsNullOrWhiteSpace(val.ToString())) val = "false";
                            info.SetValue(listitem, Convert.ChangeType(val, typeof(bool)), null);
                        }
                        //else if (_t == typeof(Direction).ToString())
                        //{
                        //    if (val.ToString().Trim().Contains("0")) val = Direction.POSITIVE;
                        //    else val = Direction.NEGATIVE;
                        //}
                        //else if (_t == typeof(HomeMode).ToString())
                        //{
                        //    if (val.ToString().Trim().Contains("0")) val = HomeMode.ORG;
                        //    else if (val.ToString().Trim().Contains("1")) val = HomeMode.PEL;
                        //    else val = HomeMode.MEL;
                        //    info.SetValue(listitem, val, null);
                        //}
                        else
                        {
                            info.SetValue(listitem, val.ToString(), null);
                        }
                    }
                    list.Add(listitem);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.StackTrace);
            }
            return list;
        }

        public static DataTable ExcuteCmd_DataTable(string cmdText)
        {
            DataTable dt = new DataTable();
            try
            {
                if (_connection.State != ConnectionState.Open)
                    _connection.Open();

                SQLiteCommand cmd = _connection.CreateCommand();
                cmd.CommandText = cmdText;
                SQLiteDataReader dr = cmd.ExecuteReader();
                cmd.Dispose();

                if (dr.HasRows)
                {
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        dt.Columns.Add(dr.GetName(i));
                    }
                    while (dr.Read())
                    {
                        DataRow row = dt.NewRow();
                        for (int i = 0; i < dr.FieldCount; i++)
                        {
                            var val = dr[i];
                            row[i] = val;
                        }
                        dt.Rows.Add(row);
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.StackTrace);
            }
            return dt;
        }

        #endregion
    }
}
